Hyperlink in excel to all subfolders in a folder+PowerQuery+VBA
HTML-код
- Опубликовано: 16 июл 2024
- hyperlink in Excel to all subfolders - Using PowerQuery and VBA for an effectively dynamic solution to hyperlinking to all subfolders in a folder in Excel
Link to the workbook:
drive.google.com/file/d/1XWo9...
00:00 Introduction
00:23 Problem/Challenge description
01:20 Showing how to create a hyperlink
01:58 Using Get Data to get a list of subfolders
04:00 Making the solution more dynamic
06:11 Using the hyperlink function for dynamic hyperlinks
07:20 Showing a hyperlink to a file in folder
09:00 using ChatGPT for simple VBA code to open folder dialog
09:30 Adding VBA code to Excel workbook
11:34 Insert button to run code
12:32 Concluding Thoughts
As always Victor, very elegant resolution. "Small bricks" joined together creating a "large building".
Thank you!!
That's exactly how I put it to someone on Linkedin, a combination of several simple ideas
Excellent!
Awesome as always!
Great technique, Victor. Thank you.
I was going to advice an additional check for presence/absence of trailing slash in the path (a user can put it or vise versa). But you solved this possible scenario by an elegant macro of folder selecting. Thumbs up 👍
Absolutely, that's something I was going to add, but like you rightly observed, with VBA, we would not need to include that bit, but users can always be funny and may still choose to type in manually and cause issues
Thanks for the video, very interesting.
Is it possible to create the hyperlink column and its formula in PowerQuery before loading the data into excel ?
interesting question. That would be cool if it could be done, meaning PQ would have to have a hyperlink function and one can set it up in there, but I doubt that's possible, i just don't like saying IMPOSSIBLE (as at today) 😁 but once set-up, the 2nd column, the hyperlink column is always there and you don't need to do anymore to get the range to contract/expand as necessary